import sqlite3
import json
from datetime import datetime

# 在文件顶部添加导入
from utils.config_manager import config

def create_resume_table(db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    """
    创建简历库表
    """
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS resume_library (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                create_time TEXT NOT NULL,
                name TEXT NOT NULL,
                ctno TEXT NOT NULL,
                datajson TEXT NOT NULL
            )
        """)
        conn.commit()


def insert_resume(name, ctno, data, user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    """
    插入简历数据
    :param db_path: 数据库路径
    :param user_id: 用户ID
    :param name: 姓名
    :param ctno: 联系方式（如手机号或微信号）
    :param data: 简历数据（字典格式）
    """
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    data_json = json.dumps(data, ensure_ascii=False)

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO resume_library (userId, create_time, name, ctno, datajson)
            VALUES (?, ?, ?, ?, ?)
        """, (user_id, create_time, name, ctno, data_json))
        conn.commit()


def get_user_byDb(ctno, user_id=1, db_path=None):
    db_path = db_path if db_path else config.get_db_path()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # 首先查找用户记录
        cursor.execute("""
              SELECT id, datajson FROM resume_library WHERE ctno = ? AND userId = ?
          """, (ctno, user_id))

        row = cursor.fetchone()
        if row is None:
            print(f"未找到ctno为{ctno}的用户记录")
            return False
        return True

def update_user_wechat_number(ctno, wechat_number, user_id=1, db_path=None):
    """
    根据ctno更新用户简历中的微信号
    :param ctno: 联系方式（用于查找用户）
    :param wechat_number: 微信号
    :param user_id: 用户ID
    :param db_path: 数据库路径
    """
    db_path = db_path if db_path else config.get_db_path()

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        
        # 首先查找用户记录
        cursor.execute("""
            SELECT id, datajson FROM resume_library WHERE ctno = ? AND userId = ?
        """, (ctno, user_id))
        
        row = cursor.fetchone()
        if row is None:
            print(f"未找到ctno为{ctno}的用户记录")
            return False
            
        record_id, data_json = row
        
        try:
            # 解析JSON数据
            data = json.loads(data_json)
            if 'personal_info' in data and data['personal_info'].get('wechat'):
                print(f"用户{ctno}已有微信号: {data['personal_info']['wechat']}，跳过更新")
                return True
            
            # 更新personal_info中的微信号
            if 'personal_info' not in data:
                data['personal_info'] = {}
            
            data['personal_info']['wechat'] = wechat_number
            
            # 将更新后的数据转换回JSON
            updated_data_json = json.dumps(data, ensure_ascii=False)
            update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            # 更新数据库记录
            cursor.execute("""
                UPDATE resume_library SET datajson = ?, create_time = ? WHERE id = ?
            """, (updated_data_json, update_time, record_id))
            
            conn.commit()
            print(f"成功更新用户{ctno}的微信号为: {wechat_number}")
            return True
            
        except json.JSONDecodeError as e:
            print(f"解析JSON数据失败: {e}")
            return False
        except Exception as e:
            print(f"更新微信号时出错: {e}")
            return False

def get_list_by_name(name,user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT * FROM resume_library WHERE name = ? AND userId = ?
        """, (name, user_id))
        rows = cursor.fetchall()
        return rows

def get_list_by_id(name,user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    with sqlite3.connect(db_path) as conn:  
        cursor = conn.cursor()
        cursor.execute("""
            SELECT * FROM resume_library WHERE ctno = ? AND userId = ?
        """, (name, user_id))
        rows = cursor.fetchall()
        return rows

def get_list(user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    with sqlite3.connect(db_path) as conn:      
        cursor = conn.cursor()
        cursor.execute("""
            SELECT create_time, datajson FROM resume_library WHERE  userId = ?
            ORDER BY create_time DESC
        """, ( user_id,))
        rows = cursor.fetchall()
        res_data = []
        for row in rows:
            data_json = json.loads(row[1])
            tm = row[0]
            data_json['createTime'] = tm
            res_data.append(data_json)

        return res_data


def get_list_by_params(search_params, user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    """
    根据条件查询简历列表
    :param search_params: 查询参数字典，包含 startTime, endTime, name
    :param user_id: 用户ID
    :param db_path: 数据库路径
    :return: 查询结果列表，每个元素为字典
    """
    search_params = json.loads(search_params)
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()

        # 基础查询语句，只选择 create_time 和 datajson，与 get_list 保持一致
        query = """
            SELECT create_time, datajson 
            FROM resume_library 
            WHERE userId = ?
        """
        params = [user_id]

        # 处理 name 条件
        if search_params.get('name'):
            query += " AND name LIKE ?"
            params.append(f"%{search_params['name']}%")

        # 处理时间范围条件
        if search_params.get('startTime'):
            query += " AND create_time >= ?"
            params.append(search_params['startTime'])

        if search_params.get('endTime'):
            query += " AND create_time <= ?"
            params.append(search_params['endTime'])

        # 添加排序
        query += " ORDER BY create_time DESC"
        print(query)
        # 执行查询
        cursor.execute(query, params)
        rows = cursor.fetchall()

        # 处理结果，与 get_list 输出格式保持一致
        res_data = []
        for row in rows:
            data_json = json.loads(row[1])
            tm = row[0]
            data_json['createTime'] = tm
            res_data.append(data_json)

        return res_data

def update_resume(name, ctno, data, user_id=1, db_path=None):
    # 使用config获取数据库路径
    db_path = db_path if db_path else config.get_db_path()
    """
    更新简历数据
    :param name: 姓名
    :param ctno: 联系方式（如手机号或微信号）
    :param data: 简历数据（字典格式）
    :param user_id: 用户ID
    :param db_path: 数据库路径
    :return: 更新是否成功
    """
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    data_json = json.dumps(data, ensure_ascii=False)
    
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE resume_library 
            SET name = ?, datajson = ?, create_time = ?
            WHERE ctno = ? AND userId = ?
        """, (name, data_json, create_time, ctno, user_id))
        conn.commit()
        
        # 检查是否有行被更新
        if cursor.rowcount > 0:
            print(f"成功更新用户{ctno}的简历数据")
            return True
        else:
            print(f"未找到ctno为{ctno}的用户记录，无法更新")
            return False

create_resume_table()